-- init characters -------------------------
-- 创建表
DROP TABLE IF EXISTS "characters" CASCADE;  
CREATE TABLE IF NOT EXISTS "characters" (
    "guid" UUID DEFAULT uuid_generate_v4(),
    "id" SMALLINT NOT NULL,
    "exp" BIGINT DEFAULT 0,
    PRIMARY KEY (guid)
);
-- 表属性
-- 表说明
COMMENT ON TABLE characters IS '角色信息表';
-- 字段说明
COMMENT ON COLUMN characters.guid IS '角色GUID';
COMMENT ON COLUMN characters.id IS '角色ID';
COMMENT ON COLUMN characters.exp IS '角色经验值';
-- init characters -------------------------

-- characters function ---------------------------
-- 添加账号
CREATE OR REPLACE FUNCTION "characters_insert"(IN char_id SMALLINT)
RETURNS UUID AS $$
DECLARE
    char_guid UUID;
BEGIN
    -- 添加characters记录
    INSERT INTO characters (id)
         VALUES (char_id) RETURNING guid INTO char_guid;
    RETURN char_guid;
END;
$$ 
LANGUAGE plpgsql;

-- 用GUID查询角色
CREATE OR REPLACE FUNCTION "characters_select_by_guid"(IN char_guid UUID)
RETURNS characters AS $$
DECLARE
    result characters;
BEGIN
    SELECT * INTO result FROM characters WHERE guid = char_guid;
    PERFORM characters_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 合并characters结果到cache
CREATE OR REPLACE FUNCTION "characters_merge_to_cache"(IN tab characters)
RETURNS INTEGER AS $$
DECLARE
    tmp_key_guid text;
    tmp_guid text;
    tmp_value text[];
BEGIN
    IF tab IS NULL THEN
        RETURN NULL;
    END IF;

    SELECT regexp_replace(''||tab.guid, '-','','g') INTO tmp_guid;

    tmp_key_guid = 'characters:guid_'||tmp_guid;
    tmp_value = '{
        guid,       '||tmp_guid||',
        id,         '||tab.id||',
        exp,        '||tab.exp||'
    }';

    LOOP
        UPDATE characters_cache
            SET val = tmp_value
            WHERE key = tmp_key_guid;
        IF found THEN
            RETURN NULL;
        END IF;

        BEGIN
            INSERT INTO characters_cache(key, val) VALUES (tmp_key_guid, tmp_value);
            RETURN NULL;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;
-- characters function ---------------------------


-- characters cache -------------------------
DROP FOREIGN TABLE IF EXISTS "characters_cache" CASCADE;
CREATE FOREIGN TABLE "characters_cache" (key text, val text[])
	SERVER redis_server
	OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'characters:');
-- characters cache -------------------------


-- trigger function -------------------------
CREATE OR REPLACE FUNCTION "characters_cache_sync"()
RETURNS TRIGGER AS $$
BEGIN
    RETURN (SELECT characters_merge_to_cache(NEW));
END;
$$
LANGUAGE plpgsql;
-- trigger function -------------------------


-- trigger -------------------------
DROP TRIGGER IF EXISTS characters_cache_update ON characters CASCADE;
CREATE TRIGGER characters_cache_update
	AFTER UPDATE ON characters
	FOR EACH ROW EXECUTE PROCEDURE characters_cache_sync();
-- trigger -------------------------